const { WorkOrder, Employee, TaskAssignment, User } = require('../../database/index')
const { getExecutedSql } = require('../../logger');
const moment = require('moment'); // 引入moment.js库  
// const Sequelize = require('sequelize')
const {sequelize,Sequelize} = require('../../database/init')
// 导出
module.exports = async (req, res) =>
{
    const operation = req.params.operation;
    const { searchType, pageNum_w = 1, // 默认页码为1
        pageSize_w = 5, pageNum_u = 1, // 默认页码为1
        pageSize = 5, pageNum = 1,
        pageSize_u = 5, workOrderName, workOrderNumber, workOrderStatus, createdDate, income, workOrderDescription, approver, workOrderLevel, dueDate, createdUserID, userId, workOrderID } = req.body
    try {
        function generateTicketNumber ()
        {
            const prefix = "TKC"; // 工单编号前缀  
            const suffix = "0000"; // 工单编号后缀  

            // 获取当前时间戳（毫秒级别）  
            const timestamp = new Date().getTime();

            // 将时间戳转换为字符串并保留6位小数  
            const timestampStr = timestamp.toString().padStart(14, '0');

            // 获取最后一位数字作为流水号  
            const serialNumber = parseInt(timestampStr[13]);

            // 生成流水号的补位  
            const padding = "000000".substring(0, 6 - (serialNumber + 1).toString().length) + (serialNumber + 1);

            // 组合生成工单编号  
            const ticketNumber = `${prefix}${timestampStr}${padding}${suffix}`;

            return ticketNumber;
        }
        switch (operation) {
            case 'create':
                // // 根据客户端传递过来的 用户名 查询 数据库 中 是否 存在这用户名
                const state = {
                    WorkOrderName: workOrderName,
                    WorkOrderDescription: workOrderDescription,
                    CreatedDate: new Date(),
                    DueDate: null,  //到期日
                    Status: 0,
                    Income: income,
                    Level: workOrderLevel,
                    Update: null,
                    CreatedUserID: createdUserID,  //发布人ID
                    OrderNumber: generateTicketNumber(),
                    Approver: null //处理人
                }
                const newWorkOrderData = await WorkOrder.create(state)
                const data = await WorkOrder.findOne({
                    where: {
                        WorkOrderID: newWorkOrderData.WorkOrderID
                    }
                })
                res.status(200).send({
                    data,
                    meta: {
                        msg: "创建成功",
                        status: 200
                    },
                    sql: getExecutedSql()
                })
                break;
            case 'read':
                // 实现分页
                let whereCondition = {}; // 默认查询条件为空对象
                if (searchType === "exact" && workOrderName) {
                    // 执行精确搜索
                    whereCondition = { WorkOrderName: { [Sequelize.Op.eq]: workOrderName } };
                } else if (searchType === "fuzzy" && workOrderName) {
                    // 执行模糊搜索
                    whereCondition = { WorkOrderName: { [Sequelize.Op.like]: `%${workOrderName}%` } };
                }
                // 计算偏移量和页大小
                const offset_w = (pageNum_w - 1) * pageSize_w;
                const offset_u = (pageNum_u - 1) * pageSize_u;


                // 查询已分配的工单
                const assignedWorkOrders = await TaskAssignment.findAndCountAll({
                    limit: Number(pageSize_w),
                    offset: Number(offset_w),
                    include: [
                        {
                            model: WorkOrder,
                            where: whereCondition,
                            as: 'workOrderData',
                            attributes: ['WorkOrderName', 'Status', 'Income', 'WorkOrderDescription', 'OrderNumber', 'Approver', 'Level', 'Update', 'DueDate', 'CreatedDate'],
                            include: [
                                {
                                    model: Employee,
                                    as: 'createdUserName',
                                    attributes: ['Name', 'EmployeeID'],
                                    required: false, // 允许没有匹配的员工信息
                                },
                            ],
                        },
                        {
                            model: Employee,
                            as: 'assignedToUserName',
                            attributes: ['Name', 'EmployeeID'],
                        },
                    ],
                });

                // 查询未分配的工单
                const unassignedWorkOrderData = await WorkOrder.findAndCountAll({
                    limit: Number(pageSize_u),
                    offset: Number(offset_u),
                    where: {
                        [Sequelize.Op.and]: [
                            {
                                WorkOrderID: {
                                    [Sequelize.Op.notIn]: Sequelize.literal('(SELECT WorkOrderID FROM TaskAssignment)'),
                                }
                            },
                            whereCondition
                        ]
                    },
                });

                const totalAssignedWorkOrders = assignedWorkOrders.count;
                const totalUnassignedWorkOrders = unassignedWorkOrderData.count;

                res.status(200).send({
                    data: {
                        workOrderData: {
                            workOrderData: assignedWorkOrders.rows, //已经分配的工单
                            total: totalAssignedWorkOrders,
                            pageSize: Number(pageSize_w),
                            pageNum: Number(pageNum_w),
                        },
                        unassignedWorkOrderData: {
                            unassignedWorkOrderData: unassignedWorkOrderData.rows,
                            total: totalUnassignedWorkOrders,
                            pageSize: Number(pageSize_u),
                            pageNum: Number(pageNum_u),
                        }
                    },
                    meta: {
                        msg: "获取成功！",
                        status: 200
                    },
                    sql: getExecutedSql()
                });

                break;
            case 'update':
                const stateData = {
                    WorkOrderName: workOrderName,
                    WorkOrderDescription: workOrderDescription,
                    Level: workOrderLevel,
                    DueDate: dueDate,  //到期日
                    Status: workOrderStatus,
                    Income: income,
                    Update: new Date(),
                    CreatedUserID: createdUserID,  //创建者ID
                    Approver: approver ? approver : null
                }
                const upWorkOrderIDData = await WorkOrder.update(stateData, { where: { WorkOrderID: workOrderID } },)
                res.status(200).send({
                    meta: {
                        msg: "编辑成功",
                        status: 200
                    },
                    sql: getExecutedSql()
                })
                break;
            case 'readAll':
                // 查询已分配的工单
                const tableData = await TaskAssignment.findAndCountAll({
                    include: [
                        {
                            model: WorkOrder,
                            as: 'workOrderData',
                            attributes: ['WorkOrderName', 'Status', 'Income', 'WorkOrderDescription', 'Level'],
                            include: [
                                {
                                    model: Employee,
                                    as: 'createdUserName',
                                    attributes: ['Name', 'EmployeeID'],
                                    required: false, // 允许没有匹配的员工信息
                                },
                            ],
                        },
                        {
                            model: Employee,
                            as: 'assignedToUserName',
                            attributes: ['Name', 'EmployeeID'],
                        },
                    ],
                });
                // 查询未分配的工单
                const untableData = await WorkOrder.findAndCountAll({
                    where: {
                        [Sequelize.Op.and]: [
                            {
                                WorkOrderID: {
                                    [Sequelize.Op.notIn]: Sequelize.literal('(SELECT WorkOrderID FROM TaskAssignment)'),
                                }
                            }
                        ]
                    },
                });
                res.status(200).send({
                    data: {
                        tableData,
                        untableData
                    },
                    meta: {
                        msg: "获取成功",
                        status: 200
                    },
                    sql: getExecutedSql()
                })
                break;
            case 'delete':
                const delData = await WorkOrder.destroy({ where: { WorkOrderID: workOrderID } })
                res.status(200).send({
                    meta: {
                        msg: "删除成功",
                        status: 200
                    },
                    sql: getExecutedSql()
                })
                break
            case 'findAllWorkOrderList':
                const workOrderNames = await WorkOrder.findAll({
                    attributes: ['WorkOrderName'] // 只选择用户名字段
                });
                const workOrderNameList = workOrderNames.map(dep => dep.WorkOrderName);
                // 打印sql语句
                res.status(200).send({
                    workOrderNameList,
                    meta: {
                        msg: "获取成功！",
                        status: 200
                    },
                    sql: getExecutedSql()
                })
                break;
            case 'findWorkOrderAndID':
                const workOrderAndID = await Employee.findAll({
                    attributes: ['Name', "EmployeeID"] // 只选择用户名字段
                });
                // 打印sql语句
                res.status(200).send({
                    workOrderAndID,
                    meta: {
                        msg: "获取成功！",
                        status: 200
                    },
                    sql: getExecutedSql()
                })
                break;
            // 获取当前用户创建的工单
            case 'findworkOrderByUserID':
                let whereC = {};
                let whereB = {};
                if (createdUserID) {
                    whereC.CreatedUserID = createdUserID
                }
                else {
                    whereB.EmployeeID = userId
                }
                console.log("whereC",whereC);
                
                const result = await TaskAssignment.findAndCountAll({
                    include: [
                        {
                            model: WorkOrder,
                            as: 'workOrderData',
                            include: [
                                {
                                    model: Employee,
                                    as: 'createdUserName',
                                    attributes: ['Name', 'EmployeeID', 'user_id'],
                                    required: false, // 允许没有匹配的员工信息
                                    include: [
                                        {
                                            model: User,

                                        }
                                    ],

                                },
                            ],
                            where: whereC
                        },
                        {
                            model: Employee,
                            as: 'assignedToUserName',
                            attributes: ['Name', 'EmployeeID'],
                            where: whereB
                        },
                    ],
                });
                console.log("---->",result);
                // 打印sql语句  
                res.status(200).send({
                    data: result,
                    meta: {
                        msg: "获取成功！",
                        status: 200
                    },
                    sql: getExecutedSql()
                });
                break;
            // 获取当前用户已处理的工单
            case "findworkOrderByApprover":
                let whereCondition_a = {}
                if (workOrderNumber) {
                    whereCondition_a.OrderNumber = workOrderNumber
                }
                if (workOrderStatus) {
                    whereCondition_a.Status = workOrderStatus
                }
                if (workOrderLevel) {
                    whereCondition_a.Level = workOrderLevel
                }
                if (workOrderName) {
                    whereCondition_a.WorkOrderName = workOrderName
                }
                const offset_a = (pageNum - 1) * pageSize
                const resultByApprover = await TaskAssignment.findAll({
                    limit: Number(pageSize),
                    offset: Number(offset_a),
                    include: [
                        {
                            model: WorkOrder,
                            as: 'workOrderData',
                            where: {
                                Approver: approver,
                                ...whereCondition_a
                            },
                            include: [
                                {
                                    model: User,
                                },
                                {
                                    model: User,
                                    as: "CreatedUser"
                                }
                            ]
                        }
                    ],
                });
                const resultByApproverAll = await TaskAssignment.findAll({
                    include: [
                        {
                            model: WorkOrder,
                            as: 'workOrderData',
                            where: {
                                Approver: approver,
                                ...whereCondition_a
                            },
                            include: [
                                {
                                    model: User,
                                },
                                {
                                    model: User,
                                    as: "CreatedUser"
                                }
                            ]
                        }
                    ],
                });

                const resultTotal_a = await TaskAssignment.count({
                    include: [
                        {
                            model: WorkOrder,
                            as: 'workOrderData',
                            where: {
                                Approver: approver,
                                ...whereCondition_a
                            },
                            include: [
                                {
                                    model: User,
                                },
                                {
                                    model: User,
                                    as: "CreatedUser"
                                }
                            ]
                        }
                    ],
                });
                // 打印sql语句  
                res.status(200).send({
                    data: {
                        result: resultByApprover,
                        resultAll: resultByApproverAll,
                        total: resultTotal_a,
                        pageNum: Number(pageNum),
                        pageSize: Number(pageSize),
                    },
                    meta: {
                        msg: "获取成功！",
                        status: 200
                    },
                    sql: getExecutedSql()
                });
                break;
            // 获取当前用户超时的工单
            case "findworkOrderByOverTime":
                let whereCondition_o = {}
                if (workOrderNumber) {
                    whereCondition_o.OrderNumber = workOrderNumber
                }
                if (workOrderStatus) {
                    whereCondition_o.Status = workOrderStatus
                }
                if (workOrderLevel) {
                    whereCondition_o.Level = workOrderLevel
                }
                if (workOrderName) {
                    whereCondition_o.WorkOrderName = workOrderName
                }
                const offset_o = (pageNum - 1) * pageSize

                // 获取当前日期  
                const now = moment().format('YYYY-MM-DD');

                // 定义查询条件  
                const queryConditions = {
                    DueDate: {
                        [Sequelize.Op.lt]: now // DueDate小于当前日期  
                    },
                    // Approver: approver,
                    ...whereCondition_o,
                };
                const resultByOverTime = await TaskAssignment.findAll({
                    limit: Number(pageSize),
                    offset: Number(offset_o),
                    include: [
                        {
                            model: WorkOrder,
                            as: 'workOrderData',
                            where: {
                                // Approver: approver,
                                ...queryConditions
                            },
                            include: [
                                {
                                    model: User,
                                },
                                {
                                    model: User,
                                    as: "CreatedUser"
                                }
                            ]
                        }
                    ],
                });
                const resultByOverTimeAll = await TaskAssignment.findAll({
                    include: [
                        {
                            model: WorkOrder,
                            as: 'workOrderData',
                            where: {
                                // Approver: approver,
                                ...queryConditions
                            },
                            include: [
                                {
                                    model: User,
                                },
                                {
                                    model: User,
                                    as: "CreatedUser"
                                }
                            ]
                        }
                    ],
                });
                const resultTotal_o = await TaskAssignment.count({
                    include: [
                        {
                            model: WorkOrder,
                            as: 'workOrderData',
                            where: {
                                // Approver: approver,
                                ...queryConditions
                            },
                            include: [
                                {
                                    model: User,
                                },
                                {
                                    model: User,
                                    as: "CreatedUser"
                                }
                            ]
                        }
                    ],
                });

                // 打印sql语句  
                res.status(200).send({
                    data: {
                        result: resultByOverTime,
                        resultAll: resultByOverTimeAll,
                        total: resultTotal_o,
                        pageNum: Number(pageNum),
                        pageSize: Number(pageSize),
                    },
                    meta: {
                        msg: "获取成功！",
                        status: 200
                    },
                    sql: getExecutedSql()
                });
                break
            // 获取当前用户已创建的工单
            case "findworkOrderByCreate":
                let whereCondition_c = {}
                if (workOrderNumber) {
                    whereCondition_c.OrderNumber = workOrderNumber
                }
                if (workOrderLevel) {
                    whereCondition_c.Level = workOrderLevel
                }
                if (workOrderName) {
                    whereCondition_c.WorkOrderName = workOrderName
                }
                const offset_c = (pageNum - 1) * pageSize

                const resultByCreate = await WorkOrder.findAll({
                    limit: Number(pageSize),
                    offset: Number(offset_c),
                    where: {
                        [Sequelize.Op.and]: [
                            {
                                WorkOrderID: {
                                    [Sequelize.Op.notIn]: Sequelize.literal('(SELECT WorkOrderID FROM TaskAssignment)'),
                                }
                            },
                        ],
                        CreatedUserID: userId,
                        ...whereCondition_c
                    },
                    include: [
                        {
                            model: User,
                        },
                        {
                            model: User,
                            as: "CreatedUser",
                        }
                    ]
                })
                console.log("创建的工单",resultByCreate);
                
                const resultByCreateAll = await WorkOrder.findAll({
                    where: {
                        [Sequelize.Op.and]: [
                            {
                                WorkOrderID: {
                                    [Sequelize.Op.notIn]: Sequelize.literal('(SELECT WorkOrderID FROM TaskAssignment)'),
                                }
                            },
                        ],
                        CreatedUserID: userId,
                        ...whereCondition_c
                    },
                    include: [
                        {
                            model: User,
                        },
                        {
                            model: User,
                            as: "CreatedUser",
                        }
                    ]
                })
                const resultTotal_c = await WorkOrder.count({
                    where: {
                        [Sequelize.Op.and]: [
                            {
                                WorkOrderID: {
                                    [Sequelize.Op.notIn]: Sequelize.literal('(SELECT WorkOrderID FROM TaskAssignment)'),
                                }
                            },
                        ],
                        CreatedUserID: userId,
                        ...whereCondition_c
                    },
                    include: [
                        {
                            model: User,
                        },
                        {
                            model: User,
                            as: "CreatedUser",
                        }
                    ]
                })
                // 打印sql语句  
                res.status(200).send({
                    data: {
                        result: resultByCreate,
                        resultAll: resultByCreateAll,
                        total: resultTotal_c,
                        pageNum: Number(pageNum),
                        pageSize: Number(pageSize),
                    },
                    meta: {
                        msg: "获取成功！",
                        status: 200
                    },
                    sql: getExecutedSql()
                });
                break
            // 获取当前用户完成的工单数
            case "findoverworkOrderByfinish":
                let whereCondition_f = {}
                if (workOrderNumber) {
                    whereCondition_f.OrderNumber = workOrderNumber
                }
                if (workOrderLevel) {
                    whereCondition_f.Level = workOrderLevel
                }
                if (workOrderName) {
                    whereCondition_f.WorkOrderName = workOrderName
                }
                const offset_f = (pageNum - 1) * pageSize
                const employeeIDresult = await Employee.findOne({
                    where: { user_id: userId }
                })
                const employeeID = employeeIDresult.EmployeeID
                if (!employeeID) {
                    // 打印sql语句  
                    res.status(200).send({
                        meta: {
                            msg: "当前用户没有关联的员工",
                            status: 200
                        },
                    });
                } else {
                    const resultByUser = await TaskAssignment.findAll({
                        where: { AssignedToUserID: employeeID },
                        limit: Number(pageSize),
                        offset: Number(offset_f),
                        include: [
                            {
                                model: WorkOrder,
                                as: 'workOrderData',
                                where: { status: 1, ...whereCondition_f },
                                include: [
                                    {
                                        model: Employee,
                                        as: 'createdUserName',
                                        required: false, // 允许没有匹配的员工信息
                                        include: [
                                            {
                                                model: User,
                                            }
                                        ],

                                    },
                                ]
                            }
                        ],
                    });
                    const resultByUserAll = await TaskAssignment.findAll({
                        where: { AssignedToUserID: employeeID },
                        include: [
                            {
                                model: WorkOrder,
                                as: 'workOrderData',
                                where: { status: 1, ...whereCondition_f },
                                include: [
                                    {
                                        model: Employee,
                                        as: 'createdUserName',
                                        required: false, // 允许没有匹配的员工信息
                                        include: [
                                            {
                                                model: User,
                                            }
                                        ],

                                    },
                                ]
                            }
                        ],
                    });

                    const resultTotal_f = await TaskAssignment.count({
                        where: { AssignedToUserID: employeeID },
                        include: [
                            {
                                model: WorkOrder,
                                as: 'workOrderData',
                                include: [
                                    {
                                        model: Employee,
                                        as: 'createdUserName',
                                        required: false, // 允许没有匹配的员工信息
                                        include: [
                                            {
                                                model: User,
                                            }
                                        ],

                                    },
                                ]
                            }
                        ],
                    });
                    // 打印sql语句  
                    res.status(200).send({
                        data: {
                            result: resultByUser,
                            resultAll: resultByUserAll,
                            total: resultTotal_f,
                            pageNum: Number(pageNum),
                            pageSize: Number(pageSize),
                        },
                        meta: {
                            msg: "获取成功",
                            status: 200
                        },
                    });
                }
                break;
            // 获取待审核的工单
            case "findworkOrderByReview":
                let whereCondition_r = {}
                if (workOrderNumber) {
                    whereCondition_r.OrderNumber = workOrderNumber
                }
                if (workOrderStatus) {
                    whereCondition_r.Status = workOrderStatus
                }
                if (workOrderLevel) {
                    whereCondition_r.Level = workOrderLevel
                }
                if (workOrderName) {
                    whereCondition_r.WorkOrderName = workOrderName
                }
                const offset_r = (pageNum - 1) * pageSize

                // 获取当前日期  
                const now_r = moment().format('YYYY-MM-DD');

                // 定义查询条件  
                const queryConditions_r = {
                    DueDate: {
                        [Sequelize.Op.gte]: now_r // DueDate小于当前日期  
                    },
                    Approver: null,
                    ...whereCondition_r,
                };

                // 查询已分配的工单
                const resultByReview = await TaskAssignment.findAll({
                    limit: Number(pageSize),
                    offset: Number(offset_r),
                    include: [
                        {
                            model: WorkOrder,
                            where: queryConditions_r,
                            as: 'workOrderData',
                            include: [
                                {
                                    model: Employee,
                                    as: 'createdUserName',
                                    required: false, // 允许没有匹配的员工信息
                                },
                            ],
                        },
                        {
                            model: Employee,
                            as: 'assignedToUserName',
                        },
                    ],
                });
                const resultByReviewAll = await TaskAssignment.findAll({
                    include: [
                        {
                            model: WorkOrder,
                            where: queryConditions_r,
                            as: 'workOrderData',
                            include: [
                                {
                                    model: Employee,
                                    as: 'createdUserName',
                                    required: false, // 允许没有匹配的员工信息
                                },
                            ],
                        },
                        {
                            model: Employee,
                            as: 'assignedToUserName',
                        },
                    ],
                });
                const resultTotal_r = await TaskAssignment.count({
                    include: [
                        {
                            model: WorkOrder,
                            where: queryConditions_r,
                            as: 'workOrderData',
                            include: [
                                {
                                    model: Employee,
                                    as: 'createdUserName',
                                    required: false, // 允许没有匹配的员工信息
                                },
                            ],
                        },
                        {
                            model: Employee,
                            as: 'assignedToUserName',
                        },
                    ],
                });
                // 打印sql语句  
                res.status(200).send({
                    data: {
                        result: resultByReview,
                        resultAll: resultByReviewAll,
                        total: resultTotal_r,
                        pageNum: Number(pageNum),
                        pageSize: Number(pageSize),
                    },
                    meta: {
                        msg: "获取成功！",
                        status: 200
                    },
                    sql: getExecutedSql()
                });
                break

            default:
                break;
        }

    } catch (error) {
        res.status(500).send({ message: 'Internal server error', error: error.message });
    }
}
